package com.liangtian.mdm.dm.sql.service.impl;

import com.liangtian.mdm.base.dao.JdbcDao;
import com.liangtian.mdm.dm.attribute.dao.AttributeDao;
import com.liangtian.mdm.dm.attribute.model.Attribute;
import com.liangtian.mdm.dm.common.constant.TableConst;
import com.liangtian.mdm.dm.listener.CacheUtil;
import com.liangtian.mdm.dm.model.dao.ModelDao;
import com.liangtian.mdm.dm.model.model.Model;
import com.liangtian.mdm.dm.sql.enums.Column;
import com.liangtian.mdm.dm.sql.factory.SystemAttributeFactory;
import com.liangtian.mdm.dm.sql.service.GenTableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class GenTableServiceImpl implements GenTableService {

    @Autowired
    private AttributeDao attributeDao;

    @Autowired
    private ModelDao modelDao;

    @Autowired
    private JdbcDao jdbcDao;

    @Autowired
    private CacheUtil cacheUtil;

    @Override
    public boolean createTable(String model_id) {
        //模型
        Model model = modelDao.selectByPk(model_id);

        //属性
        List<Attribute> las = attributeDao.queryAttributeListByModelId(model_id);
        if (model == null || las.isEmpty()) {
            throw new RuntimeException("没有添加属性！");
        }
		
/*		//放弃建表时生成列名、表名	
		//表ID
		String tid = null;
		try {
			tid = jdbcDao.getIdsBySeq(TableConst.TABLESEQUENCE, 1)[0];
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(tid==null)
			throw new RuntimeException("--------genTable error--------");
		//列ID
		String[] colIds = null;
		try {
			colIds = jdbcDao.getIdsBySeq(TableConst.COLULMNSEQUENCE, las.size());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(colIds==null || colIds.length!=las.size())
			throw new RuntimeException("--------genTable error--------");
		String tableName = TableConst.TABLEPREFIX+tid;
*/
        String tableName = model.getTableName();
        // 生成注释sql
        List<String> comments = new ArrayList<>();//主表的表字段注释及说明

        //主表添加表说明
        comments.add("COMMENT ON TABLE " + tableName + " IS '" + nvl(model.getModelName(), model.getModelDesc()) + "'");

        // 主表sql
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ").append(tableName + "(");
        for (Attribute attr : las) {
            String column = attr.getAttrColumn();
            // 业务字段全部存为字符串，可空。
            sql.append(column + " VARCHAR2(" + dbLength(attr) + " CHAR),");
            //主表
            comments.add("COMMENT ON COLUMN " + tableName + "." + column + " IS '" + nvl(attr.getAttrName(), attr.getAttrDesc()) + "'");
        }

        //系统默认字段
        List<Column> cols = SystemAttributeFactory.getSystemColumns();
        for (Column col : cols) {
            sql.append(col.getName());
            if (!"DATETIME".equals(col.getType())) {
                sql.append(" VARCHAR2(" + col.getLength() + " CHAR)");
            } else {
                sql.append(" DATE");
            }

            if (!col.isNull()) {
                sql.append(" NOT NULL ");
            }
            sql.append(",");  //主表系统字段添加说明
            comments.add("COMMENT ON COLUMN " + tableName + "." + col.getName() + " IS '" + col.getDesc() + "'");
        }

        //变更表
        if (model.getIsModify().intValue() == 1) {
            String modifyTable = tableName + TableConst.MODIFYSUFFIX;
            //变更表表名添加注释说明

            StringBuilder modifySql = new StringBuilder();
            modifySql.append(sql.toString().replace(tableName, modifyTable));
            modifySql.append(" ORIGIN_ID INTEGER, OPERTYPE VARCHAR2(20 CHAR), ");
            modifySql.append(" PRIMARY KEY (ID) )");
            comments.add(modifySql.toString());

            comments.add("COMMENT ON TABLE " + modifyTable + " IS '" + nvl(model.getModelName() + "变更表", model.getModelDesc() + "变更表") + "'");


            //分发表中字段的说明
            for (Attribute attr : las) {
                String column = attr.getAttrColumn();
                comments.add("COMMENT ON COLUMN " + modifyTable + "." + column + " IS '" + nvl(attr.getAttrName(), attr.getAttrDesc()) + "'");
            }

            //系统默认字段
            for (Column col : cols) {
                comments.add("COMMENT ON COLUMN " + modifyTable + "." + col.getName() + " IS '" + col.getDesc() + "'");
            }

            comments.add("COMMENT ON COLUMN " + modifyTable + ".ORIGIN_ID  IS '原始数据ID'");
            comments.add("COMMENT ON COLUMN " + modifyTable + ".OPERTYPE IS '操作类型'");

            comments.add("create sequence " + modifyTable + TableConst.SEQUENCESUFFIX
                    + " minvalue 1 maxvalue 999999999 start with 1000000 increment by 1 cache 20");


        }
        //分发表
        if (model.getIsDispatch().intValue() == 1) {
            String dispatchTable = tableName + TableConst.DISPATCHSUFFIX;

            StringBuilder dispatchSql = new StringBuilder();
            dispatchSql.append(sql.toString().replace(tableName, dispatchTable));
            dispatchSql.append(" ORIGIN_ID INTEGER, ");
            dispatchSql.append(" TARGET_ID VARCHAR2(500), ");
            dispatchSql.append(" TARGET_CODE VARCHAR2(500), ");
            dispatchSql.append(" TARGET_NAME VARCHAR2(500), ");
            dispatchSql.append(" PRIMARY KEY (ID) )");

            comments.add(dispatchSql.toString());

            //分发表表名添加注释说明
            comments.add("COMMENT ON TABLE " + dispatchTable + " IS '" + nvl(model.getModelName() + "分发表", model.getModelDesc() + "分发表") + "'");

            //分发表中字段的说明
            for (Attribute attr : las) {
                String column = attr.getAttrColumn();
                comments.add("COMMENT ON COLUMN " + dispatchTable + "." + column + " IS '" + nvl(attr.getAttrName(), attr.getAttrDesc()) + "'");
            }

            //系统默认字段
            for (Column col : cols) {
                comments.add("COMMENT ON COLUMN " + dispatchTable + "." + col.getName() + " IS '" + col.getDesc() + "'");
            }


            //分发表默认字段添加字段说明
            comments.add("COMMENT ON COLUMN " + dispatchTable + ".ORIGIN_ID  IS '原始数据ID'");
            comments.add("COMMENT ON COLUMN " + dispatchTable + ".TARGET_ID IS '分发目标系统主键'");
            comments.add("COMMENT ON COLUMN " + dispatchTable + ".TARGET_CODE IS '分发系目标统编码'");
            comments.add("COMMENT ON COLUMN " + dispatchTable + ".TARGET_NAME IS '分发系目标统名称'");

            //创建分发表的表序列
            comments.add("create sequence " + dispatchTable + TableConst.SEQUENCESUFFIX
                    + " minvalue 1 maxvalue 999999999 start with 1000000 increment by 1 cache 20");


        }


        //归档表的操作
        /*STAR 添加归档表的操作功能 开始 =============================         */
        String gdTable = tableName + TableConst.GDSUFFIX;

        StringBuilder gdSql = new StringBuilder();
        gdSql.append(sql.toString().replace(tableName, gdTable));
        gdSql.append(" ORIGIN_ID VARCHAR2(20 CHAR), ");
        gdSql.append(" PRIMARY KEY (ID) )");
        comments.add(gdSql.toString());

        //归档表表名添加注释说明
        comments.add("COMMENT ON TABLE " + gdTable + " IS '" + nvl(model.getModelName() + "归档表", model.getModelDesc() + "归档表") + "'");
        comments.add("COMMENT ON COLUMN " + gdTable + ".ORIGIN_ID  IS '原始数据ID'");
        //分发表中字段的说明
        for (Attribute attr : las) {
            String column = attr.getAttrColumn();
            comments.add("COMMENT ON COLUMN " + gdTable + "." + column + " IS '" + nvl(attr.getAttrName(), attr.getAttrDesc()) + "'");
        }

        //系统默认字段
        for (Column col : cols) {
            comments.add("COMMENT ON COLUMN " + gdTable + "." + col.getName() + " IS '" + col.getDesc() + "'");
        }

        comments.add("create sequence " + gdTable + TableConst.SEQUENCESUFFIX
                + " minvalue 1 maxvalue 999999999 start with 1000000 increment by 1 cache 20");


        /*END 添加归档表的操作功能   结束 =============================         */


        sql.append(" PRIMARY KEY (ID) )");
        //执行建表sql
        comments.add(0, sql.toString());
        comments.add("create sequence " + tableName + TableConst.SEQUENCESUFFIX
                + " minvalue 1 maxvalue 999999999 start with 1000000 increment by 1 cache 20");


        //由于建表的DDL，将后续业务使用jdbcDao操作，事务保证后续业务正确的情况下再执行ddl语句
        //插入属性显示设置
        comments.add("insert into AM_ATTR_CONFIG (id, model_id, attr_id, attr_type, SHOW_ORDER, GROUP_ORDER, GROUP_ID) "
                + "select AM_ATTR_CONFIG_SEQ.nextval, tmp.*, 100, 0, ''  from ( "
                + "select model_id, id, data_type from AM_ATTRIBUTE where model_id = " + model_id + " order by id ) tmp ");
        //更新模型表
        comments.add("update AM_MODEL set is_table_create = 1 where id = " + model_id);
        //统一提交
        boolean ret = jdbcDao.executeSqlList(comments, true);

        if (ret) {
            model.setIsTableCreate(1);
            cacheUtil.addModel(model);
            // 属性信息AttrConfig需要后续设置，故等待缓存被动更新
//			cacheUtil.removeElement(model_id);
//			cacheUtil.removeAttrConfig(model_id);//移除属性设置缓存
//			cacheUtil.removeQueryCols(model_id);//移除属性列缓存
        }
        return ret;
    }

    private String nvl(String val1, String val2) {
        String val = val1;
        if (val1 == null || "".equals(val1))
            val = val2;
        if (val == null)
            val = "";
        return val;
    }

    private String dbLength(Attribute attr) {
        String len = attr.getAttrLength();
        if (len == null) {
            //文本
            if (attr.getDataType() != null && "CLOB".equals(attr.getDataType()))
                len = "4000";
                //文件存储路径
            else if (attr.getDataType() != null && "BLOB".equals(attr.getDataType()))
                len = "200";
            else // 日期
                len = "20";
        } else {
            String[] ls = len.split(",");
            if (ls.length > 1) {
                //整数+小数+小数点
                len = (Integer.parseInt(ls[0]) + Integer.parseInt(ls[1]) + 1) + "";
            }
        }
        return len;
    }

    @Override
    public boolean dropTable(String model_id) {

//		Model model = modelDao.selectByPk(model_id);
        Model model = cacheUtil.getModel(model_id);
        String tableName = model.getTableName();

        // 生成注释sql
        List<String> comments = new ArrayList<>();
        comments.add(" drop sequence " + tableName + TableConst.SEQUENCESUFFIX);
        comments.add(" drop table " + tableName);

        //归档表删除表的操作
        comments.add(" drop sequence " + tableName + TableConst.GDSUFFIX + TableConst.SEQUENCESUFFIX);
        comments.add(" drop table " + tableName + TableConst.GDSUFFIX);

        //由于建表的DDL，将后续业务使用jdbcDao操作，事务保证后续业务正确的情况下再执行ddl语句
        //删除属性显示设置
        comments.add("delete AM_ATTR_CONFIG where model_id = " + model_id);
        //删除可能存在的属性组
        comments.add("delete AM_ATTR_GROUP t where t.model_id = " + model_id);
        //更新模型表
        String sql = "update AM_MODEL set is_table_create=0";

        if ("2".equals(model.getClassType())) {
            //置空分类树的关联列
            sql += " , classcolumn = null ";
        }
        sql += " where id = " + model_id;  //, is_jsp_create=0, is_menu_create=0
        comments.add(sql);

        if (model.getIsModify().intValue() == 1) {
            //删除变更表
            comments.add(" drop sequence " + tableName + TableConst.MODIFYSUFFIX + TableConst.SEQUENCESUFFIX);
            comments.add(" drop table " + tableName + TableConst.MODIFYSUFFIX);


        }

        if (model.getIsDispatch().intValue() == 1) {
            //删除分发表
            comments.add(" drop sequence " + tableName + TableConst.DISPATCHSUFFIX + TableConst.SEQUENCESUFFIX);
            comments.add(" drop table " + tableName + TableConst.DISPATCHSUFFIX);
        }
        boolean ret = jdbcDao.executeSqlList(comments, true);

        //移除緩存
        if (ret) {
            cacheUtil.removeElement(model_id);
            cacheUtil.removeAttrConfig(model_id);//移除属性设置缓存
            cacheUtil.removeQueryCols(model_id);//移除属性列缓存
        }
        return ret;
    }

}
